﻿using BCCommon;
using BCEntity.TMS.Agent.AgentAreaRecord;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Data;

namespace BCData.TMS.Agent.AgentAreaRecord
{
    public class AgentAreaRecordData : IAgentAreaRecordData
    {
        private readonly IDatabaseContext database;

        private const string QUERY = @"SELECT A.AgentAreaRecordId,A.AgentRecordId,A.ProvinceId,A.ProvinceCode,A.CityId,A.CityCode,A.CountyId,A.CountyCode,A.IsChildAgent,A.IsEpibole,
                                            A.CreateAdminId,A.CreateAdminName,A.CreateTime,A.UpdateTime,A.EditAdminId,A.EditAdminName 
                                    FROM AgentAreaRecord A";

        public AgentAreaRecordData(IDatabaseContext database)
        {
            this.database = database;
        }

        public async Task<IEnumerable<AgentAreaRecordViewEntity>> GetViewByAgentRecordId(long agentRecordId)
        {
            string sql = @"SELECT B.ProvinceCode,B.ProvinceName,B.CityCode,B.CityName,B.CountyCode,B.CountyName 
                                FROM AgentAreaRecord A
                                INNER JOIN County B ON A.ProvinceCode=B.ProvinceCode AND A.CityCode=B.CityCode AND A.CountyCode=B.CountyCode
                                WHERE A.AgentRecordId=?AgentRecordId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);
            return await this.database.Slave.QueryAsync<AgentAreaRecordViewEntity>(sql, parameters);
        }

        public async Task<IEnumerable<AgentAreaRecordEntity>> GetByAgentRecordId(long agentRecordId)
        {
            return await database.Slave.QueryAsync<AgentAreaRecordEntity>($"{QUERY} WHERE A.AgentRecordId = ?AgentRecordId", new { agentRecordId });
        }

        public async Task<AgentAreaRecordEntity> Insert(AgentAreaRecordEntity entity)
        {
            return await database.Master.InsertAsync(entity);
        }

        public async Task<IEnumerable<AgentAreaRecordEntity>> GetByAgentRecordId(long agentRecordId, string provinceCode, string cityCode, string countyCode)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            string sql = string.Empty;
            string sqlWhere = string.Empty;
            wheres.Add("A.AgentRecordId = ?AgentRecordId");
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);
            if (!string.IsNullOrWhiteSpace(provinceCode))
            {
                wheres.Add("A.ProvinceCode = ?ProvinceCode");
                parameters.Add("?ProvinceCode", provinceCode, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(cityCode))
            {
                wheres.Add("A.CityCode = ?CityCode");
                parameters.Add("?CityCode", cityCode, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(countyCode))
            {
                wheres.Add("A.CountyCode = ?CountyCode");
                parameters.Add("?CountyCode", countyCode, DbType.AnsiString);
            }
            wheres.Add("A.IsChildAgent = FALSE");
            sqlWhere = wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty;
            sql = $" {QUERY} {sqlWhere} ORDER BY A.AgentRecordId DESC ";
            return await this.database.Slave.QueryAsync<AgentAreaRecordEntity>(sql, parameters);
        }

        public async Task<AgentAreaRecordEntity> GetByCode(string provinceCode, string cityCode, string countyCode)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            string sql = string.Empty;
            string sqlWhere = string.Empty;
            if (!string.IsNullOrWhiteSpace(provinceCode))
            {
                wheres.Add("A.ProvinceCode = ?ProvinceCode");
                parameters.Add("?ProvinceCode", provinceCode, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(cityCode))
            {
                wheres.Add("A.CityCode = ?CityCode");
                parameters.Add("?CityCode", cityCode, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(countyCode))
            {
                wheres.Add("A.CountyCode = ?CountyCode");
                parameters.Add("?CountyCode", countyCode, DbType.AnsiString);
            }
            wheres.Add("A.IsChildAgent = FALSE");
            sqlWhere = wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty;
            sql = $" {QUERY} {sqlWhere} ORDER BY A.AgentAreaRecordId DESC ";
            return await this.database.Slave.QueryFirstOrDefaultAsync<AgentAreaRecordEntity>(sql, parameters);
        }


        public async Task<AgentAreaRecordEntity> GetLastLevelByCode(string countyCode)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            string sql = string.Empty;
            string sqlWhere = string.Empty;
            if (!string.IsNullOrWhiteSpace(countyCode))
            {
                wheres.Add("A.CountyCode = ?CountyCode");
                parameters.Add("?CountyCode", countyCode, DbType.AnsiString);
            }
            wheres.Add("A.IsChildAgent = FALSE");

            sqlWhere = wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty;
            sql = $" {QUERY} {sqlWhere} ";
            return await this.database.Slave.QueryFirstOrDefaultAsync<AgentAreaRecordEntity>(sql, parameters);
        }

        public async Task<IEnumerable<AgentAreaRecordEntity>> AgentAreaExistsList(long? agentRecordId)
        {
            DynamicParameters parameters = new DynamicParameters();
            string sql = string.Format("{0} WHERE AgentRecordId=?AgentRecordId AND IsChildAgent=1", QUERY);
            if (!agentRecordId.HasValue)
            {
                sql = string.Format("{0} WHERE IsChildAgent=1", QUERY);
            }
            else
            {
                parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);
            }
            return await this.database.Slave.QueryAsync<AgentAreaRecordEntity>(sql, parameters);
        }

        public async Task<AgentAreaRecordEntity> GetByAgentRecordIdAndCountyCode(long agentRecordId, string countyCode)
        {
            string sql = string.Format(@"{0} WHERE A.AgentRecordId=?AgentRecordId AND CountyCode=?CountyCode AND IsChildAgent=0", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);
            parameters.Add("?CountyCode", countyCode, DbType.AnsiString);
            return await this.database.Slave.QueryFirstOrDefaultAsync<AgentAreaRecordEntity>(sql, parameters); 
        }

        public async Task<int> UpdateIsChildAgent(long agentAreaRecordId, string countyCode)
        {
            return await database.Master.ExecuteAsync(@"UPDATE AgentAreaRecord 
                                    SET IsChildAgent = 1 
                                    WHERE IsChildAgent = 0 AND AgentAreaRecordId <> ?AgentAreaRecordId AND CountyCode=?CountyCode",
                                    new { agentAreaRecordId, countyCode }, database.Transaction);
        }
    }
}
